python 连接oracle

您所在的位置：网站首页 › create engine › python 连接oracle

python 连接oracle

#python 连接oracle | 来源: 网络整理| 查看: 265

　　python连接oracle -- sqlalchemy

import cx_Oracle as orcl import pandas as pd from sqlalchemy import create_engine # 数据库连接 db = create_engine('oracle://qmcb:qmcb@localhost:1521/tqmcbdb') #查询 sql_select = ''' ...''' df = pd.read_sql_query(sql_select, db) #执行 db.execute('truncate table {}'.format(ttb)) #保存 df.to_sql() #太慢 #插入 conn = db.raw_connection() cursor = conn.cursor() col = ', '.join(df.columns.tolist()) s = ', '.join([':'+str(i) for i in range(1, df.shape[1]+1)]) sql = 'insert into {}({}) values({})'.format(ttb, col, s) cursor.executemany(sql, df.values.tolist()) conn.commit() cursor.close()

　　python连接oracle -- cx_Oracle

cx_oracle 使用

oracle 数据库表字段的缺失值统计 -- 基于python

cx_Oracle模块的安装和使用看下连接oracle的各种方式

import cx_Oracle as orcl #定义oracle连接 orcl.connect('load/123456@localhost/ora11g') orcl.connect('username','password','host:port/orcl') tnsname = orcl.makedsn('host', 'port', 'sid') db = orcl.connect('username', 'password', tnsname) #数据库参数输出 db.autocommit = False # 关闭自动提交 orcl.clientversion() db.version db.ping() db.username db.password db.dsn #dsn=data source name db.tnsentry cursor.rowcount #定义游标 #orcl.Cursor(db) cursor = db.cursor() #查询数据 + 执行代码（如建表，清空表） sql = "SELECT ENAME, trunc(SAL,0) FROM EMP WHERE deptno = :deptno and sal > :value" cursor.execute(sql, deptno = 30, value = 1000) dict1 = {'deptno':30, 'sal':3000} cursor.execute(sql, dict1) cursor.execute(sql) # cursor.fetchone() cursor.fetchall() cursor.fetchmany(10) #查10条数据 #插入数据 #mysql自带load data infile 'D:/../...txt' into table user(var1, ., .) cursor.execute('insert into demo(v) values(:1)',['nice']) data = [['a',1], ['b',2], ...] cursor.executemany(sql, data) #查询列名 columns = [i[0] for i in cursor.description] #出错回滚 -- 一般如插入数据时出错回滚， try:...except:db.rollback() db.rollback() #关闭数据库 cursor.close() db.close()

　　定义ConnectOracle对象

import cx_Oracle as orcl class ConnectOracle: #scott xx 192.168.32.200 1521 oracle #sqlplus, system as sysdba, select instance_name from V$instance; #oracle://qmcbrt:qmcbrt@localhost:1521/tqmcbdb #cx_Oracle.connection('hr', 'hrpwd', 'localhost:1521/XE') ##cx_Oracle.connection('qmcb:qmcb@localhost:1521/tqmcbdb') def __init__(self, username, passwd, host, port='1521', sid='oracle'): # dsn = data source name self.login = {} self.db = None self.cursor = None self.login['username'] = username self.login['passwd'] = passwd self.login['dsn'] = orcl.makedsn(host, port, sid) print(orcl.clientversion()) def connect_oracle(self): try: #orcl.connect('load/123456@localhost/ora11g') #orcl.connect('username','password','host/orcl') self.db = orcl.connect(self.login['username'], self.login['passwd'], self.login['dsn']) # 登录内搜数据库 self.db.autocommit = False # 关闭自动提交 #orcl.Cursor(self.db) self.cursor = self.db.cursor() # 设置cursor光标 #print(self.db.dsn) #print(self.db.password) #print(self.db.ping()) #print(self.db.tnsentry) #print(self.db.username); #print(self.db.version) #print(self.cursor.rowcount) return True except: print('can not connect oracle') return False def close_oracle(self): self.cursor.close() self.db.close() def select_oracle(self, sql, size=0, params=None): if self.connect_oracle(): if params: # 执行多条sql命令 # results = cur.executemany("select field from table where field = %s", ('value1', 'value2', 'value3')) #sql = "SELECT ENAME, trunc(SAL,0) FROM EMP \ # WHERE deptno = :deptno and sal > :value" #cursor.execute(sql, deptno = 30, value = 1000); #params = {'deptno':30, 'sal':3000} #cur.execute(sql, params) self.cursor.executemany(sql, params) else: self.cursor.execute(sql) if size: content = self.cursor.fetchmany(size) else: content = self.cursor.fetchall() # 字段名 #ss = sql.lower() #find默认查找从左向右第一个位置 #string = sql[ss.find('select'), ss.find('from')] columns = [i[0] for i in self.cursor.description] self.close_oracle() #if len(content)==1: # return pd.Series(content[0], index=columns) return pd.DataFrame(content, columns=columns) return False def insert_oracle(self, sql, data=None): try: self.connect_oracle() if data: # 批量插入, [(),..]、((),..) # mysql自带load data infile 'D:/../...txt' into table user(var1, ., .) #self.cursor.execute('insert into demo(v) values(:1)',['nice']) self.cursor.executemany(sql, data) else: self.cursor.execute(sql, data) except: print("insert异常") self.db.rollback() # 回滚 finally: self.db.commit() # 事务提交 self.close_oracle()

　　定义读取oracle函数

def read_db(orcl_engien, ftb, snum, enum): db = create_engine(orcl_engien) #不需要close() query_sql = ''' select * from {} where row_num > {} and row_num

【本文地址】

python 连接oracle

python 连接oracle

今日新闻

推荐新闻